--대분류 테이블 생성;
drop table first_category cascade constraint;
create table first_category(
	category_id   varchar2(20)  primary key,
	category_name varchar2(100) not null,
	fix 		  varchar2(6) not null
);


drop sequence first_category_id_seq;
create sequence first_category_id_seq
nocycle
nocache;


--기본카테고리 등록
insert into first_category(category_id, category_name, fix) values('fc-'||first_category_id_seq.nextval, '메인보드', 'true');
insert into first_category(category_id, category_name, fix) values('fc-'||first_category_id_seq.nextval, 'CPU', 'true');
insert into first_category(category_id, category_name, fix) values('fc-'||first_category_id_seq.nextval, 'RAM', 'true');
insert into first_category(category_id, category_name, fix) values('fc-'||first_category_id_seq.nextval, 'HDD', 'true');
insert into first_category(category_id, category_name, fix) values('fc-'||first_category_id_seq.nextval, 'SSD', 'true');
insert into first_category(category_id, category_name, fix) values('fc-'||first_category_id_seq.nextval, 'DVD', 'true');
insert into first_category(category_id, category_name, fix) values('fc-'||first_category_id_seq.nextval, '그래픽카드', 'true');
insert into first_category(category_id, category_name, fix) values('fc-'||first_category_id_seq.nextval, '사운드카드', 'true');
insert into first_category(category_id, category_name, fix) values('fc-'||first_category_id_seq.nextval, '운영체제', 'true');
insert into first_category(category_id, category_name, fix) values('fc-'||first_category_id_seq.nextval, '파워', 'true');
insert into first_category(category_id, category_name, fix) values('fc-'||first_category_id_seq.nextval, '케이스', 'true');
insert into first_category(category_id, category_name, fix) values('fc-'||first_category_id_seq.nextval, '쿨러', 'true');

select * from first_category;

--insert into first_category(id, name)
--	   values('fc-'||first_category_id_seq.nextval, 'CPU');

--delete from first_category;
--부품 테이블 생성
--중분류 외래키 받아옴
drop table product;
create table product(
	id          varchar2(20) primary key,
	file_name	varchar2(100),
	name	    varchar2(100) not null,
	price       number not null,
	brand       varchar2(100),
	model_no    varchar2(20) not null,
	quantity    number,
	safe_quantity number,
	description varchar2(1000),
	del         varchar2(10),
	category_name varchar2(100) not null,
	category_id  varchar2(20) not null,
	constraint product_fk_category foreign key(category_id) references first_category(category_id) on delete cascade
);

ALTER TABLE [테이블명] MODIFY [컬럼명] [변경할 컬럼 타입];

alter table product modify category_id varchar2(20) references first_category(category_id) on delete cascade;


drop sequence product_id_seq;

create sequence product_id_seq
nocache;

-- 테스트용 상품 등록
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','B85M-G IBORA', 65400, 'ASUS','b85mg', 100, '인텔(소켓1150) / (인텔) B85 / Micro-ATX(23.4 x 19.3cm) / DDR3 / PCI-Express 3.0 x16 / 8ch / D-SUB / DVI / HDMI / USB 3.0 전면 헤더 포트 지원 / WINDOWS XP 지원 안함 / 최대메모리 32GB / SATA 6Gbps 4개 / SATA 2개 / USB3.0 2개 / PS2 키보드, 마우스 포트 2개 / 전원부 3페이즈', 'false', (select category_name from first_category where category_name='메인보드'), (select category_id from first_category where category_name='메인보드'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','Z97-A STCOM', 179800, 'ASUS','z97a', 100, '인텔(소켓1150) / (인텔) Z97 / 일반-ATX(30.5 x 24.4cm) / DDR3 / CrossFire X / SLI / PCI-Express 3.0 x16 / 8ch / D-SUB / DVI / HDMI / DisplayPort / USB 3.0 전면 헤더 포트 지원 / WINDOWS XP 지원 안함 / 최대메모리 32GB / SATA 6Gbps 6개 / M.2 1개 / SATA-Express 1개 / USB 3.0 4개 / PS2 콤보포트 1개 / CPU 전원부 8페이즈', 'false', (select category_name from first_category where category_name='메인보드'), (select category_id from first_category where category_name='메인보드'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','A8 7600 카베리', 98700, 'AMD','a87600', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='CPU'), (select category_id from first_category where category_name='CPU'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','인텔 코어i5-4세대 4690', 98700, '인텔','i54thg', 100, '인텔(소켓1150) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.5GHz / 256KB / 인텔 HD 4600 / 1,200MHz / PCI-Express 16개 레인', 'false', (select category_name from first_category where category_name='CPU'), (select category_id from first_category where category_name='CPU'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','잘만 CNPS8X OPTIMA', 23370, '잘만','cnps8x', 100, 'CPU 쿨러 / 팬쿨러 / 100mm / 25T / LongLife베어링 / 4핀 / 일반형 / 구리+알루미늄 / LGA115x / LGA775 / FMx/AMx (AM1 제외)', 'false', (select category_name from first_category where category_name='쿨러'), (select category_id from first_category where category_name='쿨러'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','Hyper T4', 25500, '쿨러마스터','hypert4dy', 100, 'CPU 쿨러 / 팬쿨러 / 120mm / 25T / Rifle / 4핀 / 타워형 / 구리+알루미늄 / LGA2011 / LGA1366 / LGA115x / LGA775 / FMx/AMx (AM1 제외) / 크기 : 152.3 x 131.6 x 72.5mm', 'false', (select category_name from first_category where category_name='쿨러'), (select category_id from first_category where category_name='쿨러'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','DDR3 8G PC3-12800', 100000, 'G.SKILL','cl10ripjawsxl', 100, 'DDR3 / PC용 / 1ea / 포함 / Dimm / 8GB / 1,600MHz / XMP / 메모리 타이밍 : 10-10-10-30 / 동작전압 1.5V', 'false', (select category_name from first_category where category_name='RAM'), (select category_id from first_category where category_name='RAM'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','DDR3 8G PC3-12800', 113400, 'AVEXIR','cl9core', 100, 'DDR3 / PC용 / 2ea / 포함 / Dimm / 8GB / 1,600MHz / XMP / LED 라이트 / 메모리 타이밍 : 9-9-9-24 / 동작전압 1.50V', 'false', (select category_name from first_category where category_name='RAM'), (select category_id from first_category where category_name='RAM'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','지포스 GTX750', 164250, 'AUSU','tiocd5', 100, '지포스 GTX750 Ti / 1072 MHz , 부스트 1150MHz / PCI-Express 3.0 x16 / GDDR5(DDR5) / 5400MHz / 2GB / 128-bit / 2개 팬 / HDMI 4K 해상도 지원 / G-Sync 지원', 'false', (select category_name from first_category where category_name='그래픽카드'), (select category_id from first_category where category_name='그래픽카드'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','지포스 GTX970', 113400, 'MANLI','twinstormd5', 100, '지포스 GTX970 / 1050 MHz , 부스트 1178MHz / PCI-Express 3.0 x16 / GDDR5(DDR5) / 7010MHz / 4GB / 256-bit / 2개 팬 / 최대 모니터 4개 / HDMI 4K 해상도 지원 / 최대 148W / G-Sync 지원', 'false', (select category_name from first_category where category_name='그래픽카드'), (select category_id from first_category where category_name='그래픽카드'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','HGST 4TB Deskstar NAS', 218000, 'Hitachi','HDN724040ALE640', 100, 'HDD (PC용) / SATA3(6Gb/s) / 4TB / 7,200RPM / 메모리 64MB / 8.9cm (3.5형) / 무상 3년 / 1백만시간 MTBF (평균 무고장 시간) / RVS, TFC 센서 내장 / S.M.A.R.T 에러율 조정 / 서버용 / NAS 및 DAS 지원 / 서버 스토리지 지원 / RAID 지원 / NVR 시스템 최적화 / 빅데이터 서버 지원 / 영상편집스토리지 사용 지원 / 일반 데스크탑용 PC 사용 가능', 'false', (select category_name from first_category where category_name='HDD'), (select category_id from first_category where category_name='HDD'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','4TB GREEN', 113400, 'WD','WD40EZRX', 100, 'HDD (PC용) / SATA3(6Gb/s) / 4TB / 메모리 64MB / 8.9cm (3.5형) / 680g', 'false', (select category_name from first_category where category_name='HDD'), (select category_id from first_category where category_name='HDD'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','x110 Series SSD', 143940, 'Sandisk', 'sdx110s', 100,'SATA3(6Gb/s) / 256GB / 읽기 505MB/s , 81,000 IOPS / 쓰기 445MB/s , 44,000 IOPS / 2.5형(6.4cm) / 마벨 88SS9175 / MLC(동기식) / 두께7mm / 5년', 'false', (select category_name from first_category where category_name='SSD'), (select category_id from first_category where category_name='SSD'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','M6 PRO Series', 101000, 'Plextor','m6ps', 100, 'SATA3(6Gb/s) / 128GB / 545MB/s, 100,000 IOPS / 330MB/s, 82,000 IOPS / 2.5형(6.4cm) / 마벨 88SS9187 / MLC(토글) / 도시바A19nm / TRIM 지원 / 5년 / TRIM,S.M.A.R.T.,NCQ,ATA/ATAPI-8지원 / 70g / 100.0x69.85x6.8 mm / 캐쉬 256MB(DDR3) / AES-256bit 지원 / MTBF 2,400,000 시간', 'false', (select category_name from first_category where category_name='SSD'), (select category_id from first_category where category_name='SSD'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','CD/DVD콤보', 32350, '한국미디어','KSC-24BU', 100, 'COMBO / USB / 정품 박스 / 외장형 / CD / DVD', 'false', (select category_name from first_category where category_name='DVD'), (select category_id from first_category where category_name='DVD'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','DVD-ROM', 14750, '삼성전자','SH-118BB', 100, 'DVD-ROM / SATA / 정품 벌크 / 내장형 / 블랙 / CD / DVD / CD-ROM 52X / DVD-ROM 18X', 'false', (select category_name from first_category where category_name='DVD'), (select category_id from first_category where category_name='DVD'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','사운드블라스터Z', 138080, 'Creative','csblasterz', 100, '사운드 카드 / Creative SoundBlaster / PCI-E x1 / 3.5mm잭 / 헤드폰 단자 / 마이크 단자 / AUX-IN 단자 / LINE-IN 단자 / 디지털', 'false', (select category_name from first_category where category_name='사운드카드'), (select category_id from first_category where category_name='사운드카드'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','사운드블라스터RX', 87400, 'Creative','AUDIGY', 100, '사운드 카드 / Creative 칩셋 / 일반형 / 7.1채널 / E-MU 칩셋 / PCI-E x1 / 600옴 앰프 / 헤드폰 / 듀얼 마이크 / LINE-IN / 디지털 / ASIO', 'false', (select category_name from first_category where category_name='사운드카드'), (select category_id from first_category where category_name='사운드카드'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','Mach II 500k', 28440, 'OPTIMUS','ome500', 100, 'ATX 파워 / 표기 500W / 정격 500W / 20핀+4핀 / 120mm 팬 / 1개(팬) / +12V 다중출력 / 대기전력 1W / 하스웰 지원 / 무상 3년, 유상 4년 / 4핀 IDE x4, SATA x4, 8핀(6+2) PCI-E x2, 보조8핀(4+4) x1, FDD x1', 'false', (select category_name from first_category where category_name='파워'), (select category_id from first_category where category_name='파워'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','REXIII500w', 36900, 'POWEREX','rp3500', 100, 'ATX 파워 / 표기 500W / 정격 500W / 20핀+4핀 / 120mm 팬 / 1개(팬) / +12V 다중출력 / 대기전력 1W / 하스웰 지원 / 무상 3년, 유상 2년 / 4핀 IDE x3, SATA x5, 8핀(6+2) PCI-E x2, 보조8핀(4+4) x1, FDD x1', 'false', (select category_name from first_category where category_name='파워'), (select category_id from first_category where category_name='파워'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','Z3 PLUS', 39200, '잘만','jmz3', 100, 'PC케이스(ATX) / 미들타워 / 파워미포함 / 표준-ATX / Micro-ATX / HD AUDIO / 192mm / 465mm / 430mm / 수냉쿨링홀', 'false', (select category_name from first_category where category_name='케이스'), (select category_id from first_category where category_name='케이스'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','Cygnus Plus', 30050, '마이크로닉스','mcxcp3', 100, 'PC케이스(ATX) / 미들타워 / 파워미포함 / 표준-ATX / Micro-ATX / HD AUDIO / 185mm / 460mm / 435mm / CPU 쿨러높이:최대 165mm / 수냉쿨링홀', 'false', (select category_name from first_category where category_name='케이스'), (select category_id from first_category where category_name='케이스'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','Windows 8.1 Pro', 252000, 'Microsoft','mswin8.1', 100, '운영체제 / 제한없음 / 처음사용자용 / 한글 / 기간없음 / 선택설치 / 시작 메뉴 / 도메인 가입 / 백업 및 복원 / 터치스크린 지원 / 멀티터치 지원 / 드라이브 암호화 / 홈 그룹 / 클라우드 서비스 / 스마트 검색 / 다중 언어팩...', 'false', (select category_name from first_category where category_name='운영체제'), (select category_id from first_category where category_name='운영체제'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','Windows 7 Home Premium', 242000, 'Microsoft','mswin7hp', 100, '운영체제 / 제한없음 / 처음사용자용 / 한글 / 기간없음 / 선택설치 / 윈도우즈 / 시작 메뉴 / 터치스크린 지원 / 홈 그룹', 'false', (select category_name from first_category where category_name='운영체제'), (select category_id from first_category where category_name='운영체제'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','asd', 564747, 'ergerg', 'ewrewr', 100,'erww', 'false', (select category_name from first_category where category_name='RAM'), (select category_id from first_category where category_name='RAM'));

select * from product;

select count(id)
	from   product
	where del='false'

--update product set del='false' where del='true';

--select id, category_name categoryName, name, price, brand, model_no modelNo, quantity, description
--  		from product
--  		where name like '%윈도우%';
  		
--select 'pn-'||product_id_seq.nextval p_id, c.name, c.id from first_category c where name='가격대별';

--update product set category_name='가격대별' where category_id='fc-4';
--update first_category set category_name='가격대별' where category_id='fc-4';

--select name from product where category_name='CPU' and del='false';

alter table complete_pc modify (cnt default 0);
drop table complete_pc cascade constraint;
create table complete_pc(
	complete_id		  varchar2(20) primary key,
	file_name		  varchar2(100),
	complete_name     varchar2(100) not null,
	complete_price    number not null,
	complete_quantity number not null,
	mainboard_id	  varchar2(20) references Product(id) on delete set null,
	cpu_id			  varchar2(20) references Product(id) on delete set null,
	cooler_id		  varchar2(20) references Product(id) on delete set null,
	memory_id		  varchar2(20) references Product(id) on delete set null, 
	graphic_id  	  varchar2(20) references Product(id) on delete set null,
	hdd_id			  varchar2(20) references Product(id) on delete set null,
	ssd_id			  varchar2(20) references Product(id) on delete set null,
	dvd_id			  varchar2(20) references Product(id) on delete set null,
	sound_id		  varchar2(20) references Product(id) on delete set null,
	power_id		  varchar2(20) references Product(id) on delete set null,
	case_id           varchar2(20) references Product(id) on delete set null,
	os_id			  varchar2(20) references Product(id) on delete set null,
	event			  varchar2(10),
	cnt				  number
);

drop sequence complete_pc_id_seq;
create sequence complete_pc_id_seq
nocycle
nocache;

select * from complete_pc;

update complete_pc set complete_name='쿠조', complete_price='10000', complete_quantity='10', mainboard_id='pn-1', cpu_id='pn-3', cooler_id='pn-5', memory_id='pn-7', graphic_id='pn-9', hdd_id='pn-11', ssd_id='pn-13', dvd_id='pn-15', sound_id='pn-17', power_id='pn-19', case_id='pn-21', os_id='pn-22'
where complete_id='pc-1';

select  name mainboardName
from    product
where   id='pn-25';

select cc.file_name fileName, cc.complete_id completeId, cc.complete_name completeName, cc.complete_price completePrice, cc.complete_quantity completeQuantity, a.name mainboardName, b.name cpuName, c.name coolerName,
		d.name memoryName, e.name graphicName, f.name hddName, g.name ssdName, h.name dvdName, i.name soundName, j.name powerName, k.name caseName, l.name osName
  		from complete_pc cc, product a, product b, product c, product d, product e, product f, product g, product h, product i, product j, product k, product l
  		where complete_id='pc-1' and cc.MAINBOARD_ID=a.id(+) and cc.CPU_ID=b.id(+) and cc.COOLER_ID=c.id(+) and cc.MEMORY_ID=d.id(+) and cc.GRAPHIC_ID=e.id(+) and cc.HDD_ID=f.id(+) and cc.SSD_ID=g.id(+) and cc.DVD_ID=h.id(+) and cc.SOUND_ID=i.id(+)
  							and cc.POWER_ID=j.id(+) and cc.CASE_ID=k.id(+) and cc.OS_ID=l.id(+);

select cc.file_name fileName, cc.complete_id completeId, cc.complete_name completeName, cc.complete_price completePrice, cc.complete_quantity completeQuantity, a.name mainboardName, b.name cpuName, c.name coolerName,
		d.name memoryName, e.name graphicName, f.name hddName, g.name ssdName, h.name dvdName, i.name soundName, j.name powerName, k.name caseName, l.name osName
  		from complete_pc cc, product a, product b, product c, product d, product e, product f, product g, product h, product i, product j, product k, product l
  		where event='false' and cc.MAINBOARD_ID=a.id(+) and cc.CPU_ID=b.id(+) and cc.COOLER_ID=c.id(+) and cc.MEMORY_ID=d.id(+) and cc.GRAPHIC_ID=e.id(+) and cc.HDD_ID=f.id(+) and cc.SSD_ID=g.id(+) and cc.DVD_ID=h.id(+) and cc.SOUND_ID=i.id(+)
  							and cc.POWER_ID=j.id(+) and cc.CASE_ID=k.id(+) and cc.OS_ID=l.id(+);
  							
 select name mainboardName, name cpuName, name coolerName,
		name memoryName, name graphicName, name hddName,
		name ssdName, name dvdName, name soundName,
		name powerName, name caseName, name osName
  		from product
  		where id='pn-2';
--select complete_id completeId, complete_name completeName, complete_price completePrice, complete_quantity completeQuantity, mainboard, cpu, cooler, memory,
 -- 				graphic_card graphicCard, hdd, ssd, dvd, sound_card soundCard, pc_power pcPower, pc_case pcCase, os
--  		from complete_pc;
  		
insert into complete_pc(complete_id completeId, complete_name completeName, 
				complete_price completePrice, complete_quantity completeQuantity, 
				mainboard, cpu, cooler, memory, graphic_card graphicCard, hdd, ssd, 
				dvd, sound_card soundCard, pc_power pcPower, pc_case pcCase, os)
 values('pc-2', null,'완제품', 500000, 5, '기가바이트', '인텔', '쿨러', '64', '지포스660x', '삼성1T', '없음', '잇음', '내장형', 'power', '풍운','window7');
